<?php
defined('IN_HRLOO') or exit('Access Denied');

class fx_sql_mysql{
	/**
	 * 数据库连接资源句柄
	 * @var resource
	 */
	public $link;  
	public $dbcfg;//数据库配置
	public $shared_key;
	public $dbname; //数据库名字
	public $querynum;
	
	private static $link_pool; //共享的连接池
	
	private static $instance_pool; //共享的实例池
	
	private static $cur_db;//todo:a bug ,cur_db应该是每个连接一个。
	
	/**
	 * 随机获取一个从DB配置
	 * @param array $dbcfg_list
	 * @return dbcfg
	 */
	public static function GetSlaveCfgRandom($dbcfg_list){
		$slaveCfg = $dbcfg_list[0];
		if(count($dbcfg_list) < 2) return $slaveCfg;
		
		$max = -1;
		foreach($dbcfg_list as $v){
			$w = rand(1,65535) * (isset($v['slave_weight']) ? $v['slave_weight'] : 1);
			if($w>$max){
				$max = $w;
				$slaveCfg=$v;
			}
		}
	}
	
	/**
	 * 随机获取一个从DB
	 * @param array $dbcfg_list DB配置列表
	 * @return fx_sql_mysql
	 */
	public static function GetSlaveDbRandom($dbcfg_list){
		return new fx_sql_mysql(self::GetSlaveCfgRandom($dbcfg_list));
	}
	
	/**
	 * 获取第一个从库，如果没有配置从库则获取主库
	 * @param array $dbcfg_list DB配置列表
	 * @return fx_sql_mysql
	 */
	public static function GetFirstSlaveDb($dbcfg_list){
		$cfg = $dbcfg_list[1];
		if(!$cfg) $cfg = $dbcfg_list[0];
		if(!$cfg) throw new fx_da_exception('没找到数据库配置');
		
		return new fx_sql_mysql($cfg);
	}
	
	/**
	 * 获取主DB配置
	 * @param array $dbcfg_list DB配置列表
	 * @return fx_sql_mysql
	 */
	public static function GetMasterDb($dbcfg_list){
		return self::getInstance($dbcfg_list[0]);
	}
	
	/**
	 * 获取池中的实例
	 * @param unknown_type $dbcfg
	 * @return fx_sql_mysql
	 */
	private static function getInstance($dbcfg){
		$key = $dbcfg['host'].'$'.$dbcfg['user'].'$'.$dbcfg['pwd'].'$'.$dbcfg['dbname'];
		if(!isset(self::$instance_pool[$key])){
			self::$instance_pool[$key] = new fx_sql_mysql($dbcfg);
		}
		return self::$instance_pool[$key];
	}
	
	/**
	 * 关闭，连接池中的所有连接
	 */
	public static function ClosePoolLink(){
		foreach(self::$link_pool as $link){
			mysql_close($link);
		}
	}
	
	/**
	 * 构造函数，使用DB配置
	 * @param array $dbcfg DB配置
	 */
	private function __construct($dbcfg){
		$this->dbcfg = $dbcfg;
		$this->dbname = $dbcfg['dbname'];
	}
	
	/**
	 * 连接数据库，使用DB配置。
	 * @return resource
	 * @throws fx_da_exception
	 */
	public function connect(){
		$dbcfg = &$this->dbcfg;
		if(!$this->link){
			if(HDB_SHARED_STRATEGY == 1){ //数据库共享
				$shared_key = $this->shared_key = $dbcfg['host'].'$'.$dbcfg['user'].'$'.$dbcfg['pwd'];
			}else if(HDB_SHARED_STRATEGY == 2){//根据数据名共享
				$shared_key = $this->shared_key = $dbcfg['host'].'$'.$dbcfg['user'].'$'.$dbcfg['pwd'].'$'.$dbcfg['dbname'];
			}
			
			if(!empty($shared_key)){
				$this->link = self::$link_pool[$this->shared_key];
				if(!$this->link){
					$this->link = self::$link_pool[$shared_key] = mysql_connect($dbcfg['host'],$dbcfg['user'],$dbcfg['pwd']);
				}
			}else{
				$this->link = self::$link_pool[] = mysql_connect($dbcfg['host'],$dbcfg['user'],$dbcfg['pwd']);
			}
			
			if(!$this->link){
				$this->halt($this->error(),$this->errno(),'connect failed');
			}
			
		}
		if(HDB_SHARED_STRATEGY==1 && HDB_FULL_TABLENAME  != 1 && self::$cur_db != $dbcfg['dbname']){
			mysql_selectdb($dbcfg['dbname']);
		}
		return $this->link;
	}
	
	/**
	 * 插入数据
	 * @param string $table
	 * @param array $data
	 * @param int $return_insert_id
	 * @param bool $replace
	 * @return mixed
	 */
	public function insert($table, $data, $return_insert_id = false, $replace = false) {
		$sql = self::implode($data);
		$cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
		$table = $this->t($table);
		$query = $this->query("$cmd $table SET $sql");
		if($return_insert_id){
			return $this->insert_id();
		}
		return $query;	
	}
	
	/**
	 * 获取插入的自增ID
	 * @return number
	 */
	public function insert_id(){
		return mysql_insert_id($this->link);
	}
	
	/**
	 * 更新
	 * @param string $table
	 * @param array $data
	 * @param string|array $condition
	 * @param bool $unbuffered
	 * @return int 成功返回受影响的行数，失败则异常
	 * @throws fx_da_exception
	 */
	public function update($table, $data, $condition, $unbuffered = false) {
		$sql = self::implode($data);
		if(empty($sql)){
			return false;
		}

		$table = self::table($table);
		$where = '';
		if (empty($condition)) {
			$where = '1';
		} elseif (is_array($condition)) {
			$where = self::implode($condition, ' AND ');
		} else {
			$where = $condition;
		}
		self::query("UPDATE $table SET $sql WHERE $where", $unbuffered ? 'UNBUFFERED' : '');
		return $this->affected_rows();
	}
	
	/**
	 * 删除
	 * @param string $table
	 * @param mixed $condition 可以是string  / kv / array(where=>string,arg=>array)
	 * @param int $limit
	 * @param bool $unbuffered
	 * @return int
	 */
	public function delete($table, $condition, $limit = 0, $unbuffered = true) {
		if (empty($condition)) {
			return false;
		} elseif (is_array($condition)) {
			if (count($condition) == 2 && isset($condition['where']) && isset($condition['arg'])) {
				$where = $this->format($condition['where'], $condition['arg']);
			} else {
				$where = self::implode_field_value($condition, ' AND ');
			}
		} else {
			$where = $condition;
		}
		$limit = intval($limit);
		$sql = "DELETE FROM " . $this->table($table) . " WHERE $where " . ($limit > 0 ? "LIMIT $limit" : '');
		$this->query($sql);
		return $this->affected_rows();
	}
	
	/**
	 * 字段加1 update table set field=field+1 where id=id
	 * @param string $table
	 * @param string $field
	 * @param array $condition array('idfield'=>x)|直接写ID，将生成array('id'=x)
	 * @param int $inc_val 增量
	 * @example
	 * inc('tab','pl_count',2)
	 * inc('tab','pl_count',array('zlid'=>2))
	 * inc('tab','pl_count',array('zlid'=>2),3)
	 */
	public function inc($table,$field,$condition,$inc_val=1){
		$inc_val = intval($inc_val);
		$field = self::quote_field($field);
		$condition = is_array($condition) ? $condition : array('id'=>$condition);
		$key = array_keys($condition);
		$key_q = self::quote_field($key[0]);
		$val_q = self::quote($condition[$key[0]]);
		$table = $this->t($table);
		$sql = "UPDATE $table SET $field = $field + $inc_val WHERE $key_q=$val_q";
		$this->query($sql);
		return $this->affected_rows();
	}
	
	/**
	 * 返回最后一次操作影响的行数
	 * @return int
	 */
	function affected_rows() {
		return mysql_affected_rows($this->link);
	}
	
	/**
	 * 执行查询，返回查询资源ID，注意此函数不会转换%i,%t等参数，因此最好不用此方法，需要使用时要自己拼好SQL
	 * @param string $sql 需要执行的SQL语句
	 * @param bool $unbuffered 是否禁用缓存，默认为不禁用
	 * @return resource 返回Mysql查询的资源ID
	 */
	public function query($sql, $unbuffered = false) {
		if(!$this->link) $this->connect();
// 		var_dump($sql);
		$func = $unbuffered ? 'mysql_unbuffered_query' : 'mysql_query';
		if(!($query = $func($sql, $this->link))) {
			$this->halt($this->error(), $this->errno(), $sql);
		}
		$this->querynum++;
		return $query;
	}

	/**
	 * 执行SQL，使用参数格式化SQL语句,更新删除返回影响的行数
	 * 查询返回res，插入返回insert_id
	 * @param string $sql
	 * @param array $arg
	 * @return mixed
	 * @throws fx_da_exception
	 */
	public function query_format($sql, $arg = array()){
		if(!$this->link) $this->connect();
		if (!empty($arg) && is_array($arg)) {
			$sql = $this->format($sql, $arg);
		}
		$ret = $this->query($sql);
		if ($ret) {
			$cmd = trim(strtoupper(substr($sql, 0, strpos($sql, ' '))));
			if ($cmd === 'UPDATE' || $cmd === 'DELETE') {
				$ret = mysql_affected_rows($this->link);
			} elseif ($cmd === 'INSERT') {
				$ret = mysql_insert_id($this->link);
			}
		}
		return $ret;
	}
	
	/**
	 * 开始一个事务
	 */
	public function begintrans(){
		$this->query('START TRANSACTION');
	}
	
	public function commit(){
		$this->query('COMMIT');
	}
	
	public function rollback(){
		$this->query('ROLLBACK');
	}
	
	/**
	 * 释放查询结果
	 * @param resource $query_ret
	 * @return boolean
	 */
	public function free_result($query_ret){
		return mysql_free_result($query_ret);
	}

	/**
	 * 从查询ID中获取结果，即mysql_fetch_array
	 * @param resource $resourceid
	 * @param unknown_type $type
	 * @return multitype:array,bool
	 */
	public function fetch($resourceid, $type = MYSQL_ASSOC) {
		return mysql_fetch_array($resourceid, $type);
	}
	
	/**
	 * 运行SQL，获取第一条记录
	 * @param string $sql
	 * @param array $arg
	 * @return array
	 */
	public function fetch_first($sql, $arg = array()) {
		$res = $this->query_format($sql, $arg, false);
		$ret = $this->fetch($res);
		mysql_free_result($res);
		return $ret ? $ret : array();
	}
	
	/**
	 * 获取结果到数组中,失败异常
	 * @param string $sql
	 * @param array $arg
	 * @param string $keyfield
	 * @return array
	 * @throws fx_da_exception
	 */
	public function fetch_all($sql, $arg = array(), $keyfield = '') {
		$data = array();
		$query = $this->query_format($sql, $arg);
		while ($row = mysql_fetch_array($query,MYSQL_ASSOC)) {
			if ($keyfield && isset($row[$keyfield])) {
				$data[$row[$keyfield]] = $row;
			} else {
				$data[] = $row;
			}
		}
		$this->free_result($query);
		return $data;
	}
	
	/**
	 * 获取结果到数组1维中,失败异常
	 * 将select f1 from tab 中的f1直接放到1维数组
	 * @param string $sql
	 * @param array $arg
	 * @param string $keyfield
	 * @return array
	 * @throws fx_da_exception
	 */
	public function fetch_all_to_array1($sql, $arg = array()) {
		$data = array();
		$query = $this->query_format($sql, $arg);
		while ($row = mysql_fetch_array($query,MYSQL_NUM)) {
			$data[] = $row[0];
		}
		$this->free_result($query);
		return $data;
	}
	

	
	/**
	 * 获取分页数据
	 * @param string $sql
	 * @param array $arg
	 * @param int $page 从1开始
	 * @param int $pagesize
	 * @return array 'count'=>count,'data'=>array
	 */
	public function fetch_page($sql,$countsql,$arg = array(), $page, $pagesize=20){
		$ret = array();
		$fsql = $this->format($sql, $arg);
		$csql = $this->format($countsql, $arg);
		$start = ($page - 1) * $pagesize;
		$ret['count'] = $this->result($csql);
		if($ret['count'] > $start){
			$psql = $fsql.' limit '.$start.','.$pagesize;
// 			var_dump($psql);
			$ret['data'] = $this->fetch_all($psql);			
		}
		return $ret;
	}
	
	/**
	 * 默认返回第一行第一个字段的值
	 * @param string $sql
	 * @param array $arg
	 * @param int $row
	 * @return string
	 * @throws fx_da_exception
	 */
	public function result($sql,$arg=array(),$row=0){
		$ret = $this->fetch_first($sql, $arg); 
		if($ret){
			$ret= array_pop($ret);//todo: liaozc maybe a bug 
		} 
		if(count($ret)>0){
			return $ret;
		}
	}
	
	/**
	 * 根据配置给表名加上数据库的名字，即database.table
	 * @param string $table
	 * @return string
	 */
	public function table($table){
		return $this->t($table);
	}
	
	/**
	 * 根据配置给表名加上数据库的名字，即database.table
	 * @param string $table
	 * @return string
	 */
	public function t($table){
		if(HDB_FULL_TABLENAME===1){
			if(!strpos($table, '.')){
				return $this->dbname.'.'.$table;
			}
		}
		return $table;
	}
	
	/**
	 * 获取mysql错误信息
	 * @return string
	 */
	public function error() {
		return (($this->link) ? mysql_error($this->link) : mysql_error());
	}
	
	/**
	 * 获取mysql错误编号
	 * @return number
	 */
	public function errno() {
		return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
	}

	/**
	 * 抛出数据库异常
	 * @param string $message
	 * @param int $code
	 * @param string $sql
	 * @throws fx_da_exception
	 */
	function halt($message = '', $code = 0, $sql = '') {
		throw new fx_da_exception($message, $code, $sql);
	}
	
	/**
	 * 获取mysql版本号
	 * @return string
	 */
	function version(){
		if(empty($this->version)) {
			$this->version = mysql_get_server_info($this->curlink);
		}
		return $this->version;
	}
	
	/**
	 * 
	 * @param unknown_type $array
	 * @param unknown_type $glue
	 * @return string
	 */
	public static function implode($array, $glue = ',') {
		$sql = $comma = '';
		$glue = ' ' . trim($glue) . ' ';
		foreach ($array as $k => $v) {
			$sql .= $comma . self::quote_field($k) . '=' . self::quote($v);
			$comma = $glue;
		}
		return $sql;
	}
	
	/**
	 * 对数据库字段加上``
	 * @param string|array $field
	 * @return string
	 */
	public static function quote_field($field) {
		if (is_array($field)) {
			foreach ($field as $k => $v) {
				$field[$k] = self::quote_field($v);
			}
		} else {
			if (strpos($field, '`') !== false)
				$field = str_replace('`', '', $field);
			$field = '`' . $field . '`';
		}
		return $field;
	}
	
	/**
	 * 过滤掉%，将'加上\,两边加上'% %'
	 * @param string $string
	 * @return string
	 */
	public static function like_str($str,$prefix=false){
		$str = (string)$str;//todo: liaozc 不确定是否可以这样转字符串，
		$str = str_replace('%', '', $str);//去掉%
		$str = addslashes($str);
		if($prefix){
			return "'".$str."%'";
		}
		return "'%".$str."%'";
	}
	
	/**
	 * 全并字段和值
	 * @param array $array
	 * @param string $glue
	 * @return string
	 */
	public static function implode_field_value($array, $glue = ',') {
		return self::implode($array, $glue);
	}
	
	/**
	 * 拼装SQL的IN语句,不带括号
	 * @param array|mixed $val 如果不是array直接返回addcslashesr后的值
	 */
	public static function implode_sqlin($val){
		$ret = '';
		$comma = '';
		if(is_array($val)){
			foreach($val as $v){
				if (is_string($v)){
					$ret .= $comma.  '\'' . addcslashes($v, "\n\r\\'\"\032") . '\'';
					$comma = ',';
				}else if (is_int($v) or is_float($v)){
					$ret .= $comma. $v;
					$comma = ',';
				}
			}
			return $ret;
		}
	}
	
	/**
	 * 
	 * @param string|array $val
	 * @param bool $noarray
	 * @return string|array
	 */
	public static function quote($val, $noarray = false) {
	
		if (is_string($val))
			return '\'' . addcslashes($val, "\n\r\\'\"\032") . '\'';
	
		if (is_int($val) or is_float($val))
			return '\'' . $val . '\'';
	
		if (is_array($val)) {
			if($noarray === false) {
				foreach ($val as &$v) {
					$v = self::quote($v, true);
				}
				return $val;
			} else {
				return '\'\'';
			}
		}
	
		if (is_bool($val))
			return $val ? '1' : '0';
	
		return '\'\'';
	}
	
	/**
	 * 格式化SQL，使用参数
	 * @param string $sql
	 * @param array $arg
	 * @throws DbException
	 * @return string
	 */
	public function format($sql, $arg) {
		$count = substr_count($sql, '%');
		if (!$count) {
			return $sql;
		} 
		//elseif ($count > count($arg)) {
		//	throw new fx_da_exception('SQL string format error! This SQL need "' . $count . '" vars to replace into.', 0, $sql);
		//}
	
		$len = strlen($sql);
		$i = $find = 0;
		$ret = '';
		while ($i <= $len && $find < $count) {
			if ($sql{$i} == '%') {
				$next = $sql{$i + 1};
				if ($next == 't') {
					$ret .= $this->t($arg[$find]);
				} elseif ($next == 's') {
					$ret .= self::quote(is_array($arg[$find]) ? serialize($arg[$find]) : (string) $arg[$find]);
				} elseif ($next == 'f') {
					$ret .= sprintf('%F', $arg[$find]);
				} elseif ($next == 'd') {
					$ret .= intval($arg[$find]);
				} elseif ($next == 'i') {
					$ret .= $arg[$find];
				} elseif ($next == 'L') {
					$ret .= self::like_str($arg[$find]);
				} elseif ($next == 'l') {
					$ret .= self::like_str($arg[$find],true);
				} elseif ($next == 'n') {
					if (!empty($arg[$find])) {
						$ret .= is_array($arg[$find]) ? implode(',', self::quote($arg[$find])) : self::quote($arg[$find]);
					} else {
						$ret .= '0';
					}
				} elseif ($next == 'N'){
					if (!empty($arg[$find])) {
						$ret .= is_array($arg[$find]) ? self::join_int($arg[$find]) : self::quote($arg[$find]);
					} else {
						$ret .= '0';
					}						
				}
				 else {
					$ret .= self::quote($arg[$find]);
				}
				$i++;
				$find++;
			} else {
				$ret .= $sql{$i};
			}
			$i++;
		}
		if ($i < $len) {
			$ret .= substr($sql, $i);
		}
		return $ret;
	}
	
	public static function join_int($arr){
		foreach($arr as &$int){
			$int = intval($int);
		}
		return join(',',$arr);
	}
}